from typing import List

from db import Base, engine
from sqlalchemy import String, insert, update, select, delete, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, Session, relationship, sessionmaker
from common import PageParams, PageResult
from nation.dtos import NationDto


# Session = sessionmaker(engine)


class Nation(Base):
    __tablename__ = "nation"

    id: Mapped[int] = mapped_column(primary_key=True, nullable=False)
    name: Mapped[str] = mapped_column(String(200))
    location: Mapped[str] = mapped_column(String(200))


def nation_dto2model(item: NationDto):
    return Nation(id=item.id, name=item.name, location=item.location)


Base.metadata.create_all(engine)


def insert_nation(item: NationDto):
    nation = nation_dto2model(item)
    with Session(engine) as session, session.begin():
        return session.add(nation)


def update_nation(item: NationDto):
    with Session(engine) as session, session.begin():
        return session.query(Nation).filter(Nation.id == item.id).update(
            {"name": item.name, "location": item.location}
        )


def delete_nation(id: int):
    with Session(engine) as session, session.begin():
        return session.query(Nation).filter(Nation.id == id).delete()


def get_nation(id: int):
    with Session(engine) as session:
        item = session.query(Nation).filter(Nation.id == id).first()
        return NationDto.from_orm(item)


def nation_page(params: PageParams):
    with Session(engine) as session:
        offset = (params.page - 1) * params.size
        sql_where = []
        if not params and not params.params['name'] is None:
            sql_where.append(Nation.name.contains(params.params['name']))
        query = session.query(Nation).where(*sql_where)
        results = query.offset(offset).limit(params.size).all()
        data = []
        for result in results:
            data.append(NationDto.from_orm(result))

        total = query.count()
        return PageResult(page=params.page, size=params.size, total=total, list=data)
